# nn
import time
from matplotlib.pyplot import axis 
import  numpy as np
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
from joblib import dump
class MysqlUtils(object):
    def __init__(self) -> None:
       self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password="root",
            port=3306,
            db="scenic",
            charset= "utf8mb4"
        )

    def is_holiday(self,date):
        """是否是节假日"""
        if date in ['2024-09-03','2024-10-01','2024-10-02','2024-10-03','2025-01-01','2025-01-02','2025-01-03','2024-10-04','2024-10-05','2024-10-06', '2024-10-07']:
            return 1
        return 0
    
    def get_data(self):
        """获取财务数据"""
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql ="""
        select DATE(g.create_time) as date, HOUR(g.create_time) as hour, count(*) as count FROM order_user_gate_rel g   WHERE HOUR (g.create_time) BETWEEN 6 and 23 GROUP BY date, hour
        """
        cursor.execute(sql)
        ret = cursor.fetchall()
        df = pd.DataFrame(ret)
        #print(df)
        
        #格式转换
        data_range  = pd.data_range(start='2024-07-01', end='2025-01-01', freq='D')
        hours=range(6,24)
        full_index = pd.MultiINdex.from_product([date_range, hours], names=['date','hour'])
        df_full = df.set_index(['date','hour']).reindex(full_index,fill_value=0).reset_index()
        #按天组织数据， 每行包含18小时的检票次数
        df.pivot = df_full.pivot(index='date',columns='hour',values='count')
        #print(df_pivot.head)
        df_pivot['dow']= df_pivot.index.DAYOFWEEK
        df_pivot['month']= df_pivot.index.month
        df_pivot['is_holiday']= df_pivot.index.map(self.is_holiday)
        print(df_pivot.head)

        #对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow','month'], dtype=int)
        
        #归一化小时检票列
        hours_columns = list(range(6,24))
        df_hours = df_pivot[hours_columns].copy()
        
        feature_columns = [col for col in df_pivot,columns if col not in hours_columns]
        df_feature = df_pivot[feature_columns].copy()
        scaler = MinMaxScaler()
        scaled_hours = scaler.fit_transform(df_hours)
        dump(scaler, 'NN/scaler.joblib')

        #将归一化后的数据转换为DataFrame
        df_hours_scaled = pd.DataFrame(scaled_hours, columns=hours_columns, index=df_hours.index)

        #合并
        df_pivot_clean = pd.concat([df_hours_scaled,df_feature], axis=1)
        print(df_pivot_clean.head)
        df_pivot_clean.to_csv('NM/scenic_data.csv', index=False)
        
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()
